import sys
from PyQt6.QtWidgets import (
    QApplication, QMainWindow, QWidget, QVBoxLayout, QHBoxLayout,
    QGroupBox, QRadioButton, QLabel, QLineEdit, QPushButton,
    QTextEdit, QFileDialog, QMessageBox, QButtonGroup, QScrollArea
)
from PyQt6.QtCore import Qt
import openpyxl
import os

class ExcelSheetManagerApp(QMainWindow):
    def __init__(self):
        super().__init__()
        self.setWindowTitle("Excel工作表管理器")
        self.setGeometry(100, 100, 800, 700)
        
        # 主控件
        main_widget = QWidget()
        self.setCentralWidget(main_widget)
        main_layout = QVBoxLayout()
        main_widget.setLayout(main_layout)
        
        # 操作模式
        self.mode_group = QGroupBox("操作模式")
        mode_layout = QVBoxLayout()
        self.create_radio = QRadioButton("创建工作表")
        self.delete_radio = QRadioButton("删除指定工作表")
        self.delete_empty_radio = QRadioButton("删除指定空工作表")
        self.delete_all_empty_radio = QRadioButton("删除所有空工作表")
        self.extract_radio = QRadioButton("提取工作表名到TXT")
        self.delete_empty_radio.setChecked(True)
        mode_layout.addWidget(self.create_radio)
        mode_layout.addWidget(self.delete_radio)
        mode_layout.addWidget(self.delete_empty_radio)
        mode_layout.addWidget(self.delete_all_empty_radio)
        mode_layout.addWidget(self.extract_radio)
        self.mode_group.setLayout(mode_layout)
        main_layout.addWidget(self.mode_group)
        
        # 空表检查模式
        self.empty_check_group = QGroupBox("空表检查模式")
        check_layout = QVBoxLayout()
        self.strict_radio = QRadioButton("严格模式（所有单元格均为空才算空表）")
        self.loose_radio = QRadioButton("宽松模式（只要没有有效数据就算空表）")
        self.strict_radio.setChecked(True)
        check_layout.addWidget(self.strict_radio)
        check_layout.addWidget(self.loose_radio)
        self.empty_check_group.setLayout(check_layout)
        main_layout.addWidget(self.empty_check_group)
        
        # 文件选择部分
        self.txt_group = QGroupBox("TXT文件设置（包含工作表名称列表）")
        txt_layout = QVBoxLayout()
        self.txt_path_edit = QLineEdit()
        self.txt_browse_btn = QPushButton("浏览...")
        txt_file_layout = QHBoxLayout()
        txt_file_layout.addWidget(self.txt_path_edit)
        txt_file_layout.addWidget(self.txt_browse_btn)
        txt_layout.addLayout(txt_file_layout)
        self.txt_group.setLayout(txt_layout)
        main_layout.addWidget(self.txt_group)
        
        self.excel_group = QGroupBox("Excel文件设置")
        excel_layout = QVBoxLayout()
        self.excel_path_edit = QLineEdit()
        self.excel_browse_btn = QPushButton("浏览...")
        excel_file_layout = QHBoxLayout()
        excel_file_layout.addWidget(self.excel_path_edit)
        excel_file_layout.addWidget(self.excel_browse_btn)
        excel_layout.addLayout(excel_file_layout)
        self.excel_group.setLayout(excel_layout)
        main_layout.addWidget(self.excel_group)
        
        # 执行按钮
        self.execute_btn = QPushButton("执行操作")
        self.execute_btn.setStyleSheet("""
            QPushButton {
                background-color: #4CAF50;
                color: white;
                font-weight: bold;
                padding: 8px;
                border-radius: 4px;
            }
            QPushButton:hover {
                background-color: #45a049;
            }
        """)
        main_layout.addWidget(self.execute_btn)
        
        # 日志输出
        log_frame = QGroupBox("操作日志")
        log_layout = QVBoxLayout()
        self.log_area = QTextEdit()
        self.log_area.setReadOnly(True)
        self.log_area.setStyleSheet("""
            QTextEdit {
                background-color: #f5f5f5;
                border: 1px solid #ddd;
                padding: 5px;
            }
        """)
        scroll = QScrollArea()
        scroll.setWidget(self.log_area)
        scroll.setWidgetResizable(True)
        log_layout.addWidget(scroll)
        log_frame.setLayout(log_layout)
        main_layout.addWidget(log_frame)
        
        # 状态栏
        self.status_bar = self.statusBar()
        
        # 初始化状态
        self.mode_buttons = [self.create_radio, self.delete_radio, 
                            self.delete_empty_radio, self.delete_all_empty_radio,
                            self.extract_radio]
        self.update_ui()
        
        # 信号连接
        for btn in self.mode_buttons:
            btn.toggled.connect(self.update_ui)
        self.txt_browse_btn.clicked.connect(self.select_txt_file)
        self.excel_browse_btn.clicked.connect(self.select_excel_file)
        self.execute_btn.clicked.connect(self.execute_operation)
    
    def update_ui(self):
        """根据选择的模式更新UI显示"""
        current_mode = self.get_current_mode()
        
        # 显示/隐藏TXT文件组
        self.txt_group.setVisible(current_mode in [1, 2, 3])
        
        # 显示/隐藏空检查模式组
        self.empty_check_group.setVisible(current_mode in [3, 4])
        
        # 更新状态栏提示
        if current_mode == 1:
            self.status_bar.showMessage("模式：创建工作表 - 从TXT文件中读取工作表名并创建")
        elif current_mode == 2:
            self.status_bar.showMessage("模式：删除指定工作表 - 从TXT文件中读取工作表名并删除")
        elif current_mode == 3:
            self.status_bar.showMessage("模式：删除指定空工作表 - 从TXT文件中读取工作表名并删除空表")
        elif current_mode == 4:
            self.status_bar.showMessage("模式：删除所有空工作表 - 自动检查并删除所有空表")
        elif current_mode == 5:
            self.status_bar.showMessage("模式：提取工作表名 - 将Excel中所有工作表名保存到TXT文件")
    
    def get_current_mode(self):
        """获取当前选中的操作模式"""
        for i, btn in enumerate(self.mode_buttons, start=1):
            if btn.isChecked():
                return i
        return 3  # 默认返回删除指定空工作表
    
    def select_txt_file(self):
        """选择TXT文件"""
        file_path, _ = QFileDialog.getOpenFileName(
            self, "选择TXT文件", "", "文本文件 (*.txt);;所有文件 (*.*)"
        )
        if file_path:
            self.txt_path_edit.setText(file_path)
            self.log_message(f"已选择TXT文件: {file_path}")
    
    def select_excel_file(self):
        """选择Excel文件"""
        file_path, _ = QFileDialog.getOpenFileName(
            self, "选择Excel文件", "", "Excel文件 (*.xlsx);;所有文件 (*.*)"
        )
        if file_path:
            self.excel_path_edit.setText(file_path)
            self.log_message(f"已选择Excel文件: {file_path}")
    
    def log_message(self, message):
        """向日志区域添加消息"""
        self.log_area.append(message)
        self.log_area.verticalScrollBar().setValue(
            self.log_area.verticalScrollBar().maximum()
        )
        QApplication.processEvents()  # 确保UI及时更新
    
    def is_sheet_empty(self, sheet):
        """检查工作表是否为空"""
        if self.strict_radio.isChecked():  # 严格模式
            for row in sheet.iter_rows():
                for cell in row:
                    if cell.value is not None:
                        return False
            return True
        else:  # 宽松模式
            return sheet.max_row == 1 and sheet.max_column == 1 and sheet.cell(1, 1).value is None
    
    def execute_operation(self):
        """执行主操作"""
        excel_path = self.excel_path_edit.text()
        mode = self.get_current_mode()
        
        # 提取模式单独处理
        if mode == 5:
            self.handle_extract_sheets(excel_path)
            return
        
        # 验证Excel文件
        if not excel_path:
            QMessageBox.critical(self, "错误", "请先选择Excel文件")
            return
        
        if not os.path.exists(excel_path):
            QMessageBox.critical(self, "错误", f"Excel文件不存在: {excel_path}")
            return
        
        # 读取TXT文件（在需要时）
        sheet_names = []
        if mode in [1, 2, 3]:
            txt_path = self.txt_path_edit.text()
            if not txt_path:
                QMessageBox.critical(self, "错误", "请先选择TXT文件")
                return
            
            try:
                with open(txt_path, 'r', encoding='utf-8') as f:
                    sheet_names = [line.strip() for line in f if line.strip()]
            except Exception as e:
                QMessageBox.critical(self, "错误", f"无法读取TXT文件:\n{str(e)}")
                return
            
            if not sheet_names:
                QMessageBox.warning(self, "警告", "TXT文件为空或没有有效内容")
                return
        
        # 加载Excel工作簿
        try:
            workbook = openpyxl.load_workbook(excel_path)
            self.log_message(f"成功加载Excel文件: {excel_path}")
        except Exception as e:
            QMessageBox.critical(self, "错误", f"无法打开Excel文件:\n{str(e)}")
            return
        
        success_count = 0
        operation_name = ""
        action_word = ""
        total = 0
        
        try:
            if mode == 1:  # 创建模式
                self.log_message("开始创建工作表...")
                for name in sheet_names:
                    if name and name not in workbook.sheetnames:
                        workbook.create_sheet(title=name)
                        success_count += 1
                        self.log_message(f"✓ 已创建工作表: '{name}'")
                
                operation_name = "创建"
                action_word = "创建"
                total = len(sheet_names)
            
            elif mode == 2:  # 删除指定工作表
                self.log_message("开始删除指定工作表...")
                for name in sheet_names:
                    if name in workbook.sheetnames:
                        del workbook[name]
                        success_count += 1
                        self.log_message(f"✓ 已删除工作表: '{name}'")
                
                operation_name = "删除指定"
                action_word = "删除"
                total = len(sheet_names)
            
            elif mode == 3:  # 删除指定空工作表
                self.log_message("开始删除指定空工作表...")
                target_sheets = [name for name in sheet_names if name in workbook.sheetnames]
                empty_sheets = []
                
                for name in target_sheets:
                    sheet = workbook[name]
                    if self.is_sheet_empty(sheet):
                        empty_sheets.append(name)
                        self.log_message(f"检测到空工作表: '{name}'")
                    else:
                        self.log_message(f"工作表 '{name}' 非空，跳过")
                
                for name in empty_sheets:
                    del workbook[name]
                    success_count += 1
                    self.log_message(f"✓ 已删除空工作表: '{name}'")
                
                operation_name = "删除指定空"
                action_word = "删除"
                total = len(target_sheets)
            
            elif mode == 4:  # 删除所有空工作表
                self.log_message("开始删除所有空工作表...")
                target_sheets = workbook.sheetnames
                empty_sheets = []
                
                for name in target_sheets:
                    sheet = workbook[name]
                    if self.is_sheet_empty(sheet):
                        empty_sheets.append(name)
                        self.log_message(f"检测到空工作表: '{name}'")
                    else:
                        self.log_message(f"工作表 '{name}' 非空，跳过")
                
                # 避免删除所有工作表（Excel要求至少保留一个）
                if len(empty_sheets) >= len(workbook.sheetnames):
                    retain_sheet = empty_sheets[-1]
                    empty_sheets = empty_sheets[:-1]
                    self.log_message(f"警告：保留工作表 '{retain_sheet}'（Excel要求至少保留一个）")
                
                for name in empty_sheets:
                    del workbook[name]
                    success_count += 1
                    self.log_message(f"✓ 已删除空工作表: '{name}'")
                
                operation_name = "删除所有空"
                action_word = "删除"
                total = len(target_sheets)
            
            # 保存Excel文件
            try:
                workbook.save(excel_path)
                self.log_message("Excel文件已成功保存")
                
                # 显示结果
                self.status_bar.showMessage(f"{operation_name}操作完成！成功{action_word} {success_count} 个工作表")
                QMessageBox.information(
                    self, "完成",
                    f"{operation_name}操作成功！\n"
                    f"成功{action_word}的工作表: {success_count}\n"
                    f"跳过的/非空的工作表: {total - success_count}"
                )
            
            except Exception as e:
                QMessageBox.critical(self, "错误", f"保存Excel文件失败:\n{str(e)}")
                self.log_message(f"保存失败: {str(e)}")
                self.status_bar.showMessage("保存失败", 5000)
        
        except Exception as e:
            QMessageBox.critical(self, "错误", f"操作失败:\n{str(e)}")
            self.log_message(f"操作失败: {str(e)}")
            self.status_bar.showMessage("操作失败", 5000)
    
    def handle_extract_sheets(self, excel_path):
        """处理提取工作表名功能"""
        # 验证文件
        if not excel_path:
            QMessageBox.critical(self, "错误", "请先选择Excel文件")
            return
        
        if not os.path.exists(excel_path):
            QMessageBox.critical(self, "错误", f"Excel文件不存在: {excel_path}")
            return
        
        # 读取工作表名
        try:
            workbook = openpyxl.load_workbook(excel_path)
            sheet_names = workbook.sheetnames
            self.log_message(f"成功读取 {len(sheet_names)} 个工作表")
        except Exception as e:
            QMessageBox.critical(self, "错误", f"无法打开Excel文件:\n{str(e)}")
            return
        
        # 选择保存目录
        save_dir = QFileDialog.getExistingDirectory(
            self, 
            "选择保存目录",
            os.path.dirname(excel_path) if excel_path else ""
        )
        if not save_dir:
            return  # 用户取消选择
        
        # 生成文件名
        base_name = os.path.splitext(os.path.basename(excel_path))[0]
        txt_filename = f"{base_name}_所有工作表名.txt"
        save_path = os.path.join(save_dir, txt_filename)
        
        # 处理重复文件
        if os.path.exists(save_path):
            reply = QMessageBox.question(
                self,
                "文件已存在",
                f"目标文件 {txt_filename} 已存在，是否覆盖？",
                QMessageBox.StandardButton.Yes | QMessageBox.StandardButton.No
            )
            if reply != QMessageBox.StandardButton.Yes:
                return
        
        # 写入文件
        try:
            with open(save_path, 'w', encoding='utf-8') as f:
                f.write('\n'.join(sheet_names))
            
            self.log_message(f"成功保存到: {save_path}")
            self.status_bar.showMessage(f"成功提取 {len(sheet_names)} 个工作表名到 {txt_filename}")
            QMessageBox.information(
                self,
                "完成",
                f"成功提取 {len(sheet_names)} 个工作表名称到：\n{save_path}"
            )
        except Exception as e:
            QMessageBox.critical(
                self,
                "保存失败",
                f"无法保存文件:\n{str(e)}"
            )
            self.log_message(f"保存失败: {str(e)}")
            self.status_bar.showMessage("保存失败", 5000)

if __name__ == "__main__":
    app = QApplication(sys.argv)
    
    # 设置应用程序样式
    app.setStyle("Fusion")
    
    window = ExcelSheetManagerApp()
    window.show()
    sys.exit(app.exec())